﻿Imports Microsoft.Office.Interop.Excel
Imports System.Data
Imports System.Data.SqlClient
Imports Excel = Microsoft.Office.Interop.Excel
Public Class frmAD_Export

    Private Sub btnDuongDanNha_Click(sender As System.Object, e As System.EventArgs) Handles btnDuongDanNha.Click
        Dim open As New SaveFileDialog
        open.Title = "Chọn nơi lưu file export dữ liệu"
        open.Filter = "*.xlsx|*.xlsx|*.xls|*.xls"
        open.ShowDialog()
        txtDuongDanNha.Text = open.FileName
    End Sub
    Public nhaDTO As New DangTinDTO
    Public nhaBUS As New DangTinBUS
    Public tableNha As New System.Data.DataTable

    Private Sub btnTaiDS_Click(sender As System.Object, e As System.EventArgs) Handles btnTaiDS.Click
        tableNha = nhaBUS.GetAll()
        dgvBDS.DataSource = tableNha
    End Sub

    Private Sub btnExportNha_Click(sender As System.Object, e As System.EventArgs) Handles btnExportNha.Click
        If txtDuongDanNha.Text = "" Then
            MessageBox.Show("Vui lòng chọn file để export")
        Else
            If dgvBDS.Rows.Count > 0 Then
                Dim cnn As New SqlConnection
                Dim dataProvider As New DataProvider
                Dim da As SqlDataAdapter
                Dim strCon As String = "select *from DANGTIN"
                cnn = dataProvider.ConnectionData()
                da = New SqlDataAdapter(strCon, cnn)
                da.Fill(tableNha)
                '------------------------------------------------------------
                Dim xlApp As Excel.Application
                Dim xlWorkBook As Excel.Workbook
                Dim xlWorkSheet As Excel.Worksheet
                Dim misValue As Object = System.Reflection.Missing.Value
                Dim i As Integer
                Dim j As Integer

                xlApp = New Excel.Application
                xlWorkBook = xlApp.Workbooks.Add(misValue)
                xlWorkSheet = xlWorkBook.Sheets("sheet1")

                For i = 0 To dgvBDS.Rows.Count - 2
                    For j = 0 To dgvBDS.Columns.Count - 1
                        xlWorkSheet.Cells(i + 3, j + 1) = dgvBDS(j, i).Value.ToString()
                    Next
                Next

                xlWorkSheet.SaveAs(txtDuongDanNha.Text)
                xlWorkBook.Close()
                xlApp.Quit()

                releaseObject(xlApp)
                releaseObject(xlWorkBook)
                releaseObject(xlWorkSheet)
                MessageBox.Show("Thành công", "Thông báo")
            Else
                MessageBox.Show("Vui lòng load dữ liệu trước khi export")
            End If
        End If

    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

    Private Sub btnDuongDanKH_Click(sender As System.Object, e As System.EventArgs) Handles btnDuongDanKH.Click
        Dim open As New SaveFileDialog
        open.Title = "Chọn nơi lưu file export dữ liệu"
        open.Filter = "*.xlsx|*.xlsx|*.xls|*.xls"
        open.ShowDialog()
        txtDuongDanKH.Text = open.FileName
    End Sub
    Public svBUS As New SinhVienBUS
    Public tableSV As System.Data.DataTable
    Private Sub btnLuuKH_Click(sender As System.Object, e As System.EventArgs) Handles btnLuuKH.Click
        dgvSV.Rows.Clear()
        tableSV = svBUS.GetAll()
        dgvSV.DataSource = tableSV
    End Sub

    Private Sub btnImportKH_Click(sender As System.Object, e As System.EventArgs) Handles btnImportKH.Click
        If txtDuongDanKH.Text = "" Then
            MessageBox.Show("Vui lòng chọn file để export")
        Else
            If dgvSV.Rows.Count > 0 Then
                Dim cnn As New SqlConnection
                Dim dataProvider As New DataProvider
                Dim da As SqlDataAdapter
                Dim strCon As String = "select *from SINHVIEN"
                cnn = dataProvider.ConnectionData()
                da = New SqlDataAdapter(strCon, cnn)
                da.Fill(tableSV)
                '------------------------------------------------------------
                Dim xlApp As Excel.Application
                Dim xlWorkBook As Excel.Workbook
                Dim xlWorkSheet As Excel.Worksheet
                Dim misValue As Object = System.Reflection.Missing.Value
                Dim i As Integer
                Dim j As Integer

                xlApp = New Excel.Application
                xlWorkBook = xlApp.Workbooks.Add(misValue)
                xlWorkSheet = xlWorkBook.Sheets("sheet1")

                For i = 0 To dgvSV.Rows.Count - 2
                    For j = 0 To dgvSV.Columns.Count - 1
                        xlWorkSheet.Cells(i + 3, j + 1) = dgvSV(j, i).Value.ToString()
                    Next
                Next

                xlWorkSheet.SaveAs(txtDuongDanKH.Text)
                xlWorkBook.Close()
                xlApp.Quit()

                releaseObject(xlApp)
                releaseObject(xlWorkBook)
                releaseObject(xlWorkSheet)
                MessageBox.Show("Thành công", "Thông báo")
            Else
                MessageBox.Show("Vui lòng load dữ liệu trước khi export")
            End If
        End If

    End Sub
End Class